﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace taobaoSpider
{
    public static class DBHelper
    {
        #region 得到OleDbConnection对象connection
        /// <summary>
        /// 得到OleDbConnection对象connection
        /// </summary>
        private static OleDbConnection connection;
        public static OleDbConnection Connection
        {
            get
            {
                string connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+System.Windows.Forms.Application.StartupPath+"\\taobaoInfo.mdb;Persist Security Info=False";
                //string databasePassword = "";
                //string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                //string databasePassword = ConfigurationManager.ConnectionStrings["DatabasePassword"].ConnectionString;
                //databasePassword = StrTransform.Base64DeCode(databasePassword);//原字符串是可逆加密字符串
               // connectionString += databasePassword;
                if (connection == null)
                {
                    connection = new OleDbConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        #endregion

        //Sql语句访问----------------------------------------------------------------------------------------

        #region 增删改...【Sql语句访问】
        /// <summary>
        /// 增删改...【Sql语句访问】
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="pars">参数列表。选填！</param>
        /// <returns></returns>
        public static bool ExecuteCommand(string cmdText, params OleDbParameter[] pars)
        {
            using (OleDbCommand cmd = new OleDbCommand(cmdText, Connection))
            {
                cmd.Parameters.AddRange(pars);
                return cmd.ExecuteNonQuery() > 0;
            }
        }
        #endregion

        #region 增删改，支持事务[是分开的sql语句的事务]...【Sql语句访问】
        /// <summary>
        /// 增删改，支持事务[是分开的sql语句的事务]...【Sql语句访问】
        /// </summary>
        /// <param name="cmdTextArr">cmdTextArr为cmdText语句的数组</param>
        /// <param name="pars">参数列表。选填！</param>
        /// <returns></returns>
        public static bool ExecuteCommand(string[] cmdTextArr, params OleDbParameter[] pars)
        {
            OleDbTransaction tran = Connection.BeginTransaction();
            OleDbCommand cmd;
            string cmdText;
            try
            {
                for (int i = 0; i < cmdTextArr.Length; i++)//因为Access使用的是Jet-SQL，不能同时执行多条sql，所以只有分开执行
                {
                    cmdText = cmdTextArr[i];
                    cmd = new OleDbCommand(cmdText, Connection, tran);
                    cmd.Parameters.AddRange(pars);
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
        }
        #endregion

        #region 查询第一行一列值...【Sql语句访问】
        /// <summary>
        /// 查询第一行一列值...【Sql语句访问】
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="pars">参数列表。选填！</param>
        /// <returns></returns>
        public static object GetScalar(string cmdText, params OleDbParameter[] pars)
        {
            using (OleDbCommand cmd = new OleDbCommand(cmdText, Connection))
            {
                cmd.Parameters.AddRange(pars);
                return cmd.ExecuteScalar();
            }
        }
        #endregion

        #region 返回DataTable...【Sql语句访问】
        /// <summary>
        /// 返回DataTable...【Sql语句访问】
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="pars">参数列表。选填！</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string cmdText, params OleDbParameter[] pars)
        {
            DataSet ds = new DataSet();
            using (OleDbCommand cmd = new OleDbCommand(cmdText, Connection))
            {
                cmd.Parameters.AddRange(pars);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
        }
        #endregion

        //Access没有存储过程访问

    }
}
